﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.ApplicationBlocks.Data;
using DBUtility.SQLite;

namespace db2xls
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void cbxDbType_SelectedIndexChanged(object sender, EventArgs e)
        {
            cbxDbConnectionString.Items.Clear();
            switch (cbxDbType.Text)
            {
                case "MSSQL":
                    cbxDbConnectionString.Items.AddRange(new object[] {
                             "Data Source=.;Initial Catalog=test;Integrated Security=True",
                            "Data Source=.;Initial Catalog=MSSQLDB;User ID=usr;Password=pwd"
                            });
                    break;
                case "OLEDB":
                    cbxDbConnectionString.Items.AddRange(new object[] {
                            "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = D:\\Data\\mydb.mdb;",
                            "Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Uid=myUsername; Pwd=myPassword;"});
                    break;
                case "SQLite":
                    cbxDbConnectionString.Items.AddRange(new object[] {
                             "Data Source=sqlitefilename;Version=3;"});
                    break;
                default:
                    break;
            }
            cbxDbConnectionString.SelectedIndex = 0;
        }

        private void btn2dgv_Click(object sender, EventArgs e)
        {
            switch (cbxDbType.Text)
            {
                case "MSSQL":
                    dgvList.DataSource = SqlHelper.ExecuteDataset(cbxDbConnectionString.Text, CommandType.Text, txtSQL.Text).Tables[0];
                    break;
                case "OLEDB":
                    dgvList.DataSource = OleDbHelper.ExecuteDataset(cbxDbConnectionString.Text, CommandType.Text, txtSQL.Text).Tables[0];
                    break;
                case "SQLite":
                    dgvList.DataSource = SQLiteHelper.ExecuteDataSet(cbxDbConnectionString.Text, txtSQL.Text, null).Tables[0];
                    break;
                default:
                    break;
            }
        }

        private void btn2xls_Click(object sender, EventArgs e)
        {
              SaveFileDialog dlg = new SaveFileDialog();  
    dlg.Filter = "Execl files (*.xls)|*.xls";  
    dlg.FilterIndex = 0;  
    dlg.RestoreDirectory = true;  
    dlg.CreatePrompt = true;  
    dlg.Title = "保存为Excel文件";  
  
    if (dlg.ShowDialog() == DialogResult.OK)  
    {  
            switch (cbxDbType.Text)
            {
                case "MSSQL":
                    ExcelHelper.Export(
                        SqlHelper.ExecuteDataset(cbxDbConnectionString.Text, CommandType.Text, txtSQL.Text).Tables[0],
                        txtHeadText.Text,
                        dlg.FileName
                        );

                    break;
                case "OLEDB":
                    ExcelHelper.Export(
                         OleDbHelper.ExecuteDataset(cbxDbConnectionString.Text, CommandType.Text, txtSQL.Text).Tables[0],
                        txtHeadText.Text,
                        dlg.FileName
                        );
                    break;
                case "SQLite":
                    ExcelHelper.Export(
                        SQLiteHelper.ExecuteDataSet(cbxDbConnectionString.Text, txtSQL.Text, null).Tables[0],
                        txtHeadText.Text,
                        dlg.FileName
                        );
                    break;
                default:
                    break;
            }
        }
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            cbxDbType.SelectedIndex = 0;
        }

        private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            System.Diagnostics.Process.Start("http://www.connectionstrings.com/");
        }
    }
}
